IF EXISTS ( SELECT 'X' FROM dbo.sysobjects WHERE name = 'AuditPackageExecution' AND type = 'U' AND uid =1 )
BEGIN 
     DROP TABLE dbo.AuditPackageExecution
END
GO

CREATE TABLE dbo.AuditPackageExecution(
     AuditPackageExecutionId       int IDENTITY(1,1) NOT NULL,
     PackageName                   nvarchar(100)     NOT NULL,
     PackageId                     uniqueidentifier  NOT NULL,
     PackageVersionId              uniqueidentifier  NOT NULL,
     PackageVersionMajor           int               NOT NULL,
     PackageVersionMinor           int               NOT NULL,
     PackageBuildVersion           int               NOT NULL,
     ExecutionStart                datetime          NOT NULL,
     ExecutionEnd                  datetime          NOT NULL,
     ExecutionSuccessful           nchar(1)          NOT NULL,
     ParentAuditPackageExecutionId int               NULL,
     CONSTRAINT PK_AuditPackageExecution PRIMARY KEY CLUSTERED ( AuditPackageExecutionId ASC )
)
GO

CREATE NONCLUSTERED INDEX IX_AuditPackageExecution_ExecutionTime ON dbo.AuditPackageExecution ( ExecutionStart ASC, ExecutionEnd ASC )
GO
CREATE NONCLUSTERED INDEX IX_AuditPackageExecution_PackageName ON dbo.AuditPackageExecution ( PackageName ASC )
GO
CREATE NONCLUSTERED INDEX IX_AuditPackageExecution_ParentAuditPackageExecutionId ON dbo.AuditPackageExecution ( ParentAuditPackageExecutionId ASC )
GO

IF EXISTS ( SELECT 'X' FROM dbo.sysobjects WHERE name = 'AuditTableProcess' AND type = 'U' AND uid =1 )
BEGIN 
     DROP TABLE dbo.AuditTableProcess
END
GO

CREATE TABLE dbo.AuditTableProcess(
     AuditTableProcessId     int IDENTITY(1,1) NOT NULL,
     AuditPackageExecutionId int               NOT NULL,
     TableName               nvarchar(100)     NOT NULL,
     InitialRowCount         int               NOT NULL,
     InsertCount             int               NOT NULL,
     UpdateCount             int               NOT NULL,
     ErrorCount              int               NOT NULL,
     FinalRowCount           int               NOT NULL,
     ProcessSuccessful       nchar(1)          NOT NULL,
     CONSTRAINT PK_AuditTableProcess PRIMARY KEY CLUSTERED ( AuditTableProcessId ASC )
)
GO
CREATE NONCLUSTERED INDEX IX_AuditTableProcess_AuditPackageExecutionId ON dbo.AuditTableProcess ( AuditPackageExecutionId ASC )
GO
CREATE NONCLUSTERED INDEX IX_AuditTableProcess_TableName ON dbo.AuditTableProcess ( TableName ASC )
GO

ALTER TABLE dbo.AuditPackageExecution WITH CHECK ADD CONSTRAINT FK_AuditPackageExecution_AuditPackageExecution 
     FOREIGN KEY (ParentAuditPackageExecutionId) 
     REFERENCES dbo.AuditPackageExecution (AuditPackageExecutionId)
GO
ALTER TABLE dbo.AuditPackageExecution CHECK CONSTRAINT FK_AuditPackageExecution_AuditPackageExecution
GO

ALTER TABLE dbo.AuditTableProcess WITH CHECK ADD CONSTRAINT FK_AuditTableProcess_AuditPackageExecution 
     FOREIGN KEY(AuditPackageExecutionId)
     REFERENCES dbo.AuditPackageExecution (AuditPackageExecutionId)
GO
ALTER TABLE dbo.AuditTableProcess CHECK CONSTRAINT FK_AuditTableProcess_AuditPackageExecution
GO

IF EXISTS ( SELECT 'X' FROM dbo.sysobjects WHERE name = 'PackageExecutionBegin' AND type IN ( 'P', 'PC' ) AND uid = 1 )
BEGIN
     DROP PROCEDURE dbo.PackageExecutionBegin
END
GO

CREATE PROCEDURE dbo.PackageExecutionBegin
     @PackageName                   nvarchar(100),
     @PackageId                     uniqueidentifier,
     @PackageVersionId              uniqueidentifier,
     @PackageVersionMajor           int,
     @PackageVersionMinor           int,
     @PackageBuildVersion           int,
     @ExecutionStart                datetime,
     @ParentAuditPackageExecutionId int     = NULL,
     @PackageIsMasterFlag           tinyint = 0,
     @AuditPackageExecutionId       int     = NULL OUTPUT
/*
 *
 * InsertPackage
 *
 * Purpose:     Inserts a record for package execution.  Returns PackageExecutionId
 *
 * Assumes:      
 *
 * Inputs:      
 *         
 * Effects:      
 *
 * Calling
 * Application: 
 *
 * Author(s):   C. Elliott Whitlow II
 *
 * Change Log:  Initial Release 06/15/2008, 1.00.0000
 *
 */
AS
BEGIN
     SELECT @AuditPackageExecutionId = NULL

     SELECT @ParentAuditPackageExecutionId = CASE WHEN @ParentAuditPackageExecutionId <= 0
                                                  THEN NULL
                                                  ELSE @ParentAuditPackageExecutionId
                                             END

     INSERT dbo.AuditPackageExecution
          ( PackageName,
            PackageId,
            PackageVersionId,
            PackageVersionMajor,
            PackageVersionMinor,
            PackageBuildVersion,
            ExecutionStart,
            ExecutionEnd,
            ExecutionSuccessful,
            ParentAuditPackageExecutionId )
     SELECT @PackageName,
            @PackageId,
            @PackageVersionId,
            @PackageVersionMajor,
            @PackageVersionMinor,
            @PackageBuildVersion,
            @ExecutionStart,
            '01/01/1900',
            'N',
            @ParentAuditPackageExecutionId

     SELECT @AuditPackageExecutionId = @@IDENTITY

     IF ( @PackageIsMasterFlag = 1 )
     BEGIN
          UPDATE dbo.AuditPackageExecution
          SET    ParentAuditPackageExecutionId = @AuditPackageExecutionId
          FROM   dbo.AuditPackageExecution
          WHERE  AuditPackageExecutionId = @AuditPackageExecutionId
     END

     SELECT AuditPackageExecutionId = @AuditPackageExecutionId
END
GO

IF EXISTS ( SELECT 'X' FROM dbo.sysobjects WHERE name = 'PackageExecutionEnd' AND type IN ( 'P', 'PC' ) AND uid = 1 )
BEGIN
     DROP PROCEDURE dbo.PackageExecutionEnd
END
GO

CREATE PROCEDURE dbo.PackageExecutionEnd
     @AuditPackageExecutionId int,
     @ExecutionEnd            datetime = NULL,
     @ExecutionSuccessful     nchar(1)
/*
 *
 * PackageExecutionEnd
 *
 * Purpose:      Ends package execution.
 *
 * Assumes:      
 *
 * Inputs: 
 *         
 * Effects:      
 *
 * Calling
 * Application:
 *
 * Author(s):   C. Elliott Whitlow II
 *
 * Change Log:  Initial Release 06/15/2008, 1.00.0000
 *
 */
AS
BEGIN
     IF ( @ExecutionEnd IS NULL )
     BEGIN
          SELECT @ExecutionEnd = GETDATE()
     END

     UPDATE dbo.AuditPackageExecution
     SET    ExecutionEnd        = @ExecutionEnd,
            ExecutionSuccessful = @ExecutionSuccessful
     WHERE  AuditPackageExecutionId = @AuditPackageExecutionId
END
GO

IF EXISTS ( SELECT 'X' FROM dbo.sysobjects WHERE name = 'TableProcessBegin' AND type IN ( 'P', 'PC' ) AND uid = 1 )
BEGIN
     DROP PROCEDURE dbo.TableProcessBegin
END
GO

CREATE PROCEDURE dbo.TableProcessBegin
     @AuditPackageExecutionId int,
     @TableName               nvarchar(128),
     @InitialRowCount         int,
     @AuditTableProcessId     int = NULL OUTPUT
/*
 *
 * TableProcessBegin
 *
 * Purpose:      Begins table processing.  Returns AuditTableProcessId
 *
 * Assumes:      
 *
 * Inputs: 
 *         
 * Effects:      
 *
 * Calling
 * Application:
 *
 * Author(s):   C. Elliott Whitlow II
 *
 * Change Log:  Initial Release 06/15/2008, 1.00.0000
 *
 */
AS
BEGIN
     SELECT @AuditTableProcessId = NULL

     INSERT dbo.AuditTableProcess
          ( AuditPackageExecutionId,
            TableName,
            InitialRowCount,
            InsertCount,
            UpdateCount,
            ErrorCount,
            FinalRowCount,
            ProcessSuccessful )
     SELECT @AuditPackageExecutionId,
            @TableName,
            @InitialRowCount,
            0, 
            0, 
            0, 
            0, 
            'N'

     SELECT @AuditTableProcessId = @@IDENTITY
	SELECT AuditTableProcessId = @AuditTableProcessId
END
GO

IF EXISTS ( SELECT 'X' FROM dbo.sysobjects WHERE name = 'TableProcessEnd' AND type IN ( 'P', 'PC' ) AND uid = 1 )
BEGIN
     DROP PROCEDURE dbo.TableProcessEnd
END
GO

CREATE PROCEDURE dbo.TableProcessEnd
     @AuditTableProcessId int,
     @InsertCount         int,
     @UpdateCount         int,
     @ErrorCount          int,
     @FinalRowCount       int,
     @ProcessSuccessful   nchar(1)
/*
 *
 * TableProcessEnd
 *
 * Purpose:      Ends table processing.
 *
 * Assumes:      
 *
 * Inputs: 
 *         
 * Effects:      
 *
 * Calling
 * Application:
 *
 * Author(s):   C. Elliott Whitlow II
 *
 * Change Log:  Initial Release 06/15/2008, 1.00.0000
 *
 */
AS
BEGIN
     UPDATE dbo.AuditTableProcess
     SET    InsertCount       = @InsertCount,
            UpdateCount       = @UpdateCount,
            ErrorCount        = @ErrorCount,
            FinalRowCount     = @FinalRowCount,
            ProcessSuccessful = @ProcessSuccessful
     WHERE  AuditTableProcessId = @AuditTableProcessId
END
GO
